from data_access.read_db import execute_sql, execute_select
from data_access.db_conn import engine
import pandas as pd
from sqlalchemy import create_engine, MetaData, Table, Column, String, Float, Integer, Date

# 读取CSV文件
csv_file_path = '../../data-source/Data-Copilot/Data-Copilot/ResaleHDBFlatPrices/HDBPropertyInformation.csv'
df = pd.read_csv(csv_file_path)

# 定义数据库表的名称
table_name = 'hdb_property_information'

# 创建表结构（如果尚不存在）
metadata = MetaData()
table = Table(
    table_name, metadata,
    Column('blk_no', String(255)),
    Column('street', String(255)),
    Column('max_floor_lvl', Integer()),
    Column('year_completed', String(255)),
    Column('residential', String(31)),
    Column('commercial', String(31)),
    Column('market_hawker', String(31)),
    Column('miscellaneous', String(31)),
    Column('multistorey_carpark', String(31)),
    Column('precinct_pavilion', String(31)),
    Column('bldg_contract_town', String(31)),
    Column('total_dwelling_units', Integer()),
    Column('1room_sold', Integer()),
    Column('2room_sold', Integer()),
    Column('3room_sold', Integer()),
    Column('4room_sold', Integer()),
    Column('5room_sold', Integer()),
    Column('exec_sold', Integer()),
    Column('multigen_sold', Integer()),
    Column('studio_apartment_sold', Integer()),
    Column('1room_rental', Integer()),
    Column('2room_rental', Integer()),
    Column('3room_rental', Integer()),
    Column('other_room_rental', Integer()),
)

# 创建表（如果不存在）
metadata.create_all(engine)


# 准备插入数据的SQL语句
insert_query = f'INSERT INTO {table_name} (blk_no,street,max_floor_lvl,year_completed,residential,commercial,market_hawker,miscellaneous,multistorey_carpark,precinct_pavilion,bldg_contract_town,total_dwelling_units,' \
               f'1room_sold,2room_sold,3room_sold,4room_sold,5room_sold,exec_sold,multigen_sold,' \
               f'studio_apartment_sold,1room_rental,2room_rental,3room_rental,other_room_rental) VALUES ' \
               f'("%s", "%s", %s,"%s", "%s", "%s",' \
               f'"%s", "%s", "%s","%s", "%s", %s,' \
               f'%s, %s, %s,%s, %s, %s,' \
               f'%s, %s, %s,%s, %s, %s)'

# 插入数据
for index, row in df.iterrows():
    if 1:
        sql = insert_query % (
            row['blk_no'],                # String
            row['street'],                # String
            row['max_floor_lvl'],         # Integer
            row['year_completed'],        # String
            row['residential'],           # String
            row['commercial'],           # String
            row['market_hawker'],         # String
            row['miscellaneous'],        # String
            row['multistorey_carpark'],   # String
            row['precinct_pavilion'],     # String
            row['bldg_contract_town'],    # String
            row['total_dwelling_units'],  # Integer
            row['1room_sold'],            # Integer
            row['2room_sold'],            # Integer
            row['3room_sold'],           # Integer
            row['4room_sold'],           # Integer
            row['5room_sold'],          # Integer
            row['exec_sold'],           # Integer
            row['multigen_sold'],       # Integer
            row['studio_apartment_sold'], # Integer
            row['1room_rental'],         # Integer
            row['2room_rental'],        # Integer
            row['3room_rental'],       # Integer
            row['other_room_rental']   # Integer
        )
        print(sql)
        execute_sql(sql)

print("Data has been written to the database.")

